package com.info.zhiduoduo.repository.mapper.invoice;

import com.baomidou.mybatisplus.mapper.BaseMapper;
import com.info.zhiduoduo.repository.dto.invoice.InvoiceInventoryDto;
import com.info.zhiduoduo.repository.entity.invoice.InvoiceInventory;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author bob
 * @since 2020-05-24
 */
public interface InvoiceInventoryMapper extends BaseMapper<InvoiceInventory> {

	@Select(" select ii.*,ms.username,ma.realname,(select iir.entry_time from zdd_invoice_inventory_record iir " +
			" where iir.userid = ii.userid and iir.state = 2 order by id desc limit 1 ) AS lastEntryTime " +
			" from zdd_invoice_inventory ii " +
			" join zdd_maker_studio ms on ms.userid = ii.userid " +
			" join zdd_maker_account ma on ma.userid = ii.userid " +
			" where CONCAT(ms.username,ma.realname) like '%${name}%' " +
			" and if( isnull(#{dockingCity}) or #{dockingCity} = '' ,1=1 ,ma.opening_location in (0${dockingCity})) " +
			" ORDER BY lastEntryTime DESC")
	List<InvoiceInventoryDto> findList(@Param("name") String name,@Param("dockingCity")String dockingCity);

	@Select(" SELECT ii.*, ms.username, ma.realname, " +
			" (SELECT iir.entry_time FROM zdd_invoice_inventory_record iir " +
			" 		WHERE iir.userid = ii.userid AND iir.state = 2 ORDER BY id DESC LIMIT 1 ) " +
			" AS lastEntryTime, i.pendingInvoiceAmount " +
			" FROM zdd_invoice_inventory ii " +
			" LEFT JOIN zdd_maker_studio ms ON ms.userid = ii.userid " +
			" LEFT JOIN zdd_maker_account ma ON ma.userid = ii.userid " +
			" LEFT JOIN (SELECT SUM( invoice_amount ) AS pendingInvoiceAmount, userid FROM zdd_invoice " +
			"			 WHERE state != 4 AND project_type = 1 GROUP BY userid) i ON i.userid = ii.userid " +
			" WHERE CONCAT(ms.username,ma.realname) like '%${name}%' " +
			" AND ii.spe_ten_thousand = 0 AND ii.spe_hundred_thousand = 0 " +
			" AND ii.gen_ten_thousand = 0 AND ii.gen_hundred_thousand = 0 " +
			" AND i.pendingInvoiceAmount IS NOT NULL " +
			" ORDER BY lastEntryTime DESC")
	List<InvoiceInventoryDto> nothingInventoryList(@Param("name") String name);
}
